import sqlite3
import simplejson as json
class DbConnection(object):
    '''connect to a sqlite database'''
    def __init__(self):
        self.connections = {}
    def get(self, filename):
        if not self.connections.has_key(filename):
            self.connections[filename] = sqlite3.connect(filename)
        return self.connections[filename]

class MetaInfo(DbConnection):
    def getMeta(self, filename):
        con = self.get(filename)
        results = {}
        for name, value in con.cursor().execute('select name, value from metadata'):
            results[name] = value
        return results
            
class VectorDatabase(DbConnection):
    def query(self, sql, filename):
        '''return a geojson'''
        c = self.get(filename).cursor()
        c.execute(sql)
        features = []
        for f in c:
            features.append(json.loads(f[0]))
        geojson = { "type": "FeatureCollection",  "features": features}
        return geojson


class TileDatabase(DbConnection):
    '''return a map tile'''
    def getTile(self, filename, x, y, z):
        conn = self.get(filename)
        return conn.cursor().execute("""
          select tile_data from tiles
          where
            zoom_level = '%s' and
            tile_column = '%s' and
            tile_row = '%s';""" % (z, x, y)).fetchone()


def test():
    srcs = ['data/vector.sqlite', 'data/pacp.sqlite', 'data/mkii.mbtiles']
    v = VectorDatabase().query('select geojson from feature', srcs[0])
    
    print v
    for src in srcs:
        d = DbConnection().get(src)
        print d
        
    t = TileDatabase().getTile(srcs[2], '034_079', '008832_020356', 'EPSG_900913_15')
    print t    
        
